Oracle 行转列SQL后台代码

String mapSql = "";

for(String key : map.keySet()) {
  Map<String, String> tempMap = map.get(key);

  for(String tempKey : tempMap.keySet()) {
    colName = tempMap.get(tempKey);
    colCode = tempKey;
    if(!"".equals(mapSql)){
     mapSql += " UNION ALL ";
    }
    StringBuffer sqs = new StringBuffer();
    sqs.append("SELECT '"+colCode+"', T.* FROM (WITH pivot_data AS (SELECT '"+ colName +"',"+ colCode +", TAB_YEAR ");
    sqs.append("FROM US_APP."+key+")  SELECT *  FROM   pivot_data  PIVOT (SUM("+ colCode +")");
    sqs.append("FOR TAB_YEAR IN  ("+ years +" ) )) T ");
    mapSql += sqs.toString();
 }
}

List<Object[]> _querylist = genericDao.getDataWithNativeSql(mapSql);

StringBuffer bf = new StringBuffer();

if (_querylist.size() > 0) {
 for(int i = 0; i < _querylist.size(); i++) {
  Object[] obj = (Object[]) _querylist.get(i);
  int size = _listTabYear.size();
  bf.append("{codeName:'"+obj[0]+"',");
  bf.append("gdpName:'"+obj[1]+"',");

  for(int t = 0; t < size; t++){
   Object[]  tempObj = _listTabYear.get(t);
   bf.append(tempObj[0] + ":'"+obj[t+2]+"'");
   if(t == size - 1){
    bf.append("}");
   }
   bf.append(",");
  }
 }

 return "{success : true, gridData : [" + bf.substring(0, bf.length() -1) + "], fieldsCode : ["+years+"]}";
}